<template>
  <div class="excel-table">
    <!-- 显示excel表格的区域 -->
    <div id="result"
         class="result"></div>
    <!-- 显示工作表的按钮 -->
    <div class="sheet-item">
      <template v-for="(item,index) in sheetNames">
        <button :key="item"
                @click="selectSheet(index)">{{item}}</button>
      </template>
    </div>
  </div>
</template>

<script>
import XLSX from "xlsx";
export default {
  name: 'ExcelTable',
  data() {
    return {
      defaultIndex: 0, // 默认显示第一个工作表
      workBook: null, // 工作表对象
      sheetNames: [], // 工作表名称集合
    }
  },
  methods: {
    // 创建表格
    createExcelTbale(fileArraybuffer) {
      console.log(fileArraybuffer)
      if (!fileArraybuffer.byteLength) {
        this.msgError('加载文件失败，请重试！');
        return false;
      }

      // 读取excel文件数据
      this.workBook = XLSX.read(fileArraybuffer, { type: "array" });
      this.sheetNames = this.workBook.SheetNames; // 工作表名称集合
      let worksheet = this.workBook.Sheets[this.sheetNames[this.defaultIndex]]; // 读取第一张sheet
      // console.log(worksheet)
      let csv = XLSX.utils.sheet_to_csv(worksheet);
      // console.log("csv: ", csv);
      document.getElementById("result").innerHTML = this.csv2table(csv);
    },
    // 将csv转换成表格，在第一行和第一列追加类似excel的索引
    csv2table(csv) {
      // 无数据
      if (!csv) {
        return `<div class="no-data">无数据</div>`
      }

      let html = "<table class='table-xlsx'>";
      let rows = csv.split("\n");
      rows.pop(); // 最后一行没用的
      rows.forEach((row, idx) => {
        let columns = row.split(",");
        columns.unshift(idx + 1); // 添加行索引
        if (idx == 0) {
          // 添加列索引
          html += "<tr>";
          for (let i = 0; i < columns.length; i++) {
            html +=
              "<th>" +
              (i == 0 ? "" : this.reateCellPos(i - 1)) +
              "</th>";
          }
          html += "</tr>";
        }
        html += "<tr>";
        columns.forEach((column) => {
          html += "<td>" + column + "</td>";
        });
        html += "</tr>";
      });
      html += "</table>";
      return html;
    },
    // 数字转换成26进制字母EXCEL列号(把10进制数字转化成EXCEL列号)
    reateCellPos(n) {
      let ordA = 'A'.charCodeAt(0);
      let ordZ = 'Z'.charCodeAt(0);
      let len = ordZ - ordA + 1;
      let s = "";
      while (n >= 0) {
        s = String.fromCharCode(n % len + ordA) + s;
        n = Math.floor(n / len) - 1;
      }
      return s;
    },
    // 选择工作表的按钮，切换对应工作表的内容
    selectSheet(num) {
      this.defaultIndex = num;
      let worksheet = this.workBook.Sheets[this.sheetNames[this.defaultIndex]]; // 这里我们只读取第一张sheet
      // console.log(worksheet);
      let csv = XLSX.utils.sheet_to_csv(worksheet);
      // console.log("csv: ", csv);
      document.getElementById("result").innerHTML = this.csv2table(csv);
    },
  },
}
</script>

<style lang="scss" scoped>
.excel-table {
  .result {
    width: 100%;
    max-height: 600px;
    padding: 6px;
    margin: 10px 0;
    box-sizing: border-box;
    overflow: auto;
    font-size: 14px;
  }
}
/deep/.table-xlsx {
  width: 100%;
  border-collapse: collapse;
}
/deep/.table-xlsx th,
/deep/.table-xlsx td {
  border: solid 1px #6d6d6d;
  padding: 5px 10px;
  font-weight: normal;
  min-width: 120px;
}
/deep/.table-xlsx th{
  background-color: #eee;
}
/deep/.table-xlsx tr td:first-child{
  width: 60px !important;
  min-width: 60px !important;
  background-color: #eee;
}
/deep/.no-data {
  border: solid 1px #6d6d6d;
  padding: 8px;
  text-align: center;
}
</style>